﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using YiYiTong.Rocket.Helper;

namespace YiYiTong.Rocket.DataAccess
{
    public class UserDataAccess
    {
        #region 单例模式
        private UserDataAccess()
        {
        }

        private static UserDataAccess _instance;
        private static readonly object _syncObject = new object();

        public static UserDataAccess Instance
        {
            get
            {
                if (_instance == null)
                {
                    lock (_syncObject)
                    {
                        if (_instance == null)
                        {
                            _instance = new UserDataAccess();
                        }
                    }
                }
                return _instance;
            }
        }
        #endregion 单例模式

        #region 查询

        /// <summary>
        /// 根据用户登录名称获取后台用户信息
        /// </summary>
        /// <param name="loginName">登录名称</param>
        /// <returns>返回用户信息</returns>
        public DataTable GetUserInfo(string loginName)
        {
            DataTable dtUser = new DataTable();
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = "select * from UserInfo where IsDelete=0 and LoginName=@LoginName";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@LoginName", DbType.String, loginName);
                var ds = db.ExecuteDataSet(Procdbcomm);
                if (ds != null && ds.Tables.Count > 0)
                {
                    dtUser = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                //记录日志
                LogHelper.Error("根据用户登录名称获取后台用户信息-GetUserInfo" + ex.ToString());
            }
            return dtUser;
        }

        /// <summary>
        /// 根据手机号码获取后台用户信息
        /// </summary>
        /// <param name="mobile">手机号码</param>
        /// <returns>返回用户信息</returns>
        public DataTable GetUserInfoByMobile(string mobile)
        {
            DataTable dtUser = new DataTable();
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = "select * from UserInfo where IsDelete=0 and Mobile=@Mobile";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@Mobile", DbType.String, mobile);
                var ds = db.ExecuteDataSet(Procdbcomm);
                if (ds != null && ds.Tables.Count > 0)
                {
                    dtUser = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                //记录日志
                LogHelper.Error("根据手机号码获取后台用户信息-GetUserInfoByMobile" + ex.ToString());
            }
            return dtUser;
        }
        #endregion

        #region 最后登录时间与ip

        /// <summary>
        /// 修改登录时间与ip
        /// </summary>
        /// <param name="loginTime">登录时间</param>
        /// <param name="loginIp">登录ip</param>
        /// <returns></returns>
        public int EditLoginInfo(long uid, string loginTime, string loginIp)
        {
            int ret = 0;
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = @"update UserInfo set LastLoginTime =@LastLoginTime,LastLoginIp=@LastLoginIp where UID=@UID";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@LastLoginTime", DbType.String, loginTime);
                db.AddInParameter(Procdbcomm, "@LastLoginIp", DbType.String, loginIp);
                db.AddInParameter(Procdbcomm, "@UID", DbType.Int64, uid);
                ret = db.ExecuteNonQuery(Procdbcomm);
            }
            catch (Exception ex)
            {
                //记录日志
                ret = -99;
                LogHelper.Error("修改登录时间与ip-EditLoginInfo" + ex.ToString());
            }
            return ret;
        }
        #endregion

        #region 用户权限查询
        /// <summary>
        /// 获取用户菜单权限
        /// </summary>
        /// <param name="uid">用户编号</param>
        /// <returns></returns>
        public DataTable GetUserMenuList(long uid)
        {
            DataTable dtUserMenu = new DataTable("UserMenu");
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = @"SELECT DISTINCT m.menuId,m.menuCode,m.menuName,m.menuUrl,m.parentID,m.isVisible,m.createTime,m.isDelete,m.remark 
                                FROM dbo.UserRole ur
	                                INNER JOIN dbo.Role r ON r.roleId = ur.roleId
	                                INNER JOIN dbo.RoleMenu rm ON rm.roleId = r.roleId
	                                INNER JOIN dbo.Menu m ON m.menuId = rm.menuId
                                WHERE ur.uid = @UID";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@UID", DbType.Int64, uid);
                var ds = db.ExecuteDataSet(Procdbcomm);
                if (ds != null && ds.Tables.Count > 0)
                {
                    dtUserMenu = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                //记录日志
                LogHelper.Error("获取用户菜单权限-GetUserMenuList" + ex.ToString());
            }
            return dtUserMenu;
        }

        /// <summary>
        /// 获取用户功能权限
        /// </summary>
        /// <param name="uid">用户编号</param> 
        /// <returns></returns>
        public DataTable GetUserActionList(long uid)
        {
            DataTable dtUserAction = new DataTable("UserAction");
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = @"SELECT DISTINCT a.actionId ,a.actionName ,a.ControllerName ,a.description ,a.createTime ,a.isDelete ,a.remark ,a.parentId 
                                FROM dbo.UserRole ur
	                                INNER JOIN dbo.Role r ON r.roleId = ur.roleId
	                                INNER JOIN dbo.RoleAction ra ON ra.roleId = r.roleId
	                                INNER JOIN dbo.Action a ON a.actionId = ra.actionId
                                WHERE ur.uid = @UID";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@UID", DbType.Int64, uid);
                var ds = db.ExecuteDataSet(Procdbcomm);
                if (ds != null && ds.Tables.Count > 0)
                {
                    dtUserAction = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                //记录日志
                LogHelper.Error("获取用户功能权限-GetUserActionList" + ex.ToString());
            }
            return dtUserAction;
        }
        #endregion

        #region 登录日志

        /// <summary>
        /// 写入登陆日志
        /// </summary>
        /// <param name="loginName">登录名</param>
        /// <param name="loginTime">登录时间</param>
        /// <param name="loginIp">登录ip地址</param>
        /// <param name="isLogin">是否登录成功 0 失败 1 成功</param>
        /// <param name="loginDesciption">登录描述</param>
        /// <param name="remark">备注</param>
        /// <returns></returns>
        public int WriteLoginLog(string loginName, long uid, string loginTime, string loginIp, int isLogin, string loginDescription, string remark)
        {
            int ret = 0;

            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = @"INSERT INTO LoginLog( loginName,uid,loginTime,loginIp,loginDescription,isLogin,remark)
                              VALUES(@LoginName,@UID,@LoginTime,@LoginIp,@LoginDescription,@IsLogin,@Remark)";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@LoginName", DbType.String, loginName);
                db.AddInParameter(Procdbcomm, "@UID", DbType.Int64, uid);
                db.AddInParameter(Procdbcomm, "@LoginTime", DbType.String, loginTime);
                db.AddInParameter(Procdbcomm, "@LoginIp", DbType.String, loginIp);
                db.AddInParameter(Procdbcomm, "@LoginDescription", DbType.String, loginDescription);
                db.AddInParameter(Procdbcomm, "@IsLogin", DbType.Int32, isLogin);
                db.AddInParameter(Procdbcomm, "@Remark", DbType.String, remark);
                ret = db.ExecuteNonQuery(Procdbcomm);
            }
            catch (Exception ex)
            {
                //记录日志
                ret = -99;
                LogHelper.Error("写入登陆日志-WriteLoginLog" + ex.ToString());
            }
            return ret;
        }
        #endregion

        /// <summary>
        /// 根据条件筛选后台用户
        /// </summary>
        /// <param name="loginName"></param>
        /// <param name="userStatus"></param>
        /// <param name="begTime"></param>
        /// <param name="endTime"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageRows"></param>
        /// <returns></returns>
        public DataSet GetPageList(string loginName, int userStatus, string begTime, string endTime, int pageIndex, int pageRows,int RoleID)
        {
            DataSet ds = new DataSet();
            try
            {
                DBHelper db = DBFactory.AdminDB;
                DbCommand Procdbcomm = db.GetStoredProcCommand("proc_user_getlist");
                db.AddInParameter(Procdbcomm, "@loginName", DbType.String, loginName);
                db.AddInParameter(Procdbcomm, "@userStatus", DbType.Int32, userStatus);
                db.AddInParameter(Procdbcomm, "@begTime", DbType.String, begTime);
                db.AddInParameter(Procdbcomm, "@endTime", DbType.String, endTime);
                db.AddInParameter(Procdbcomm, "@pageIndex", DbType.Int32, pageIndex);
                db.AddInParameter(Procdbcomm, "@pageRows", DbType.Int32, pageRows);
                db.AddInParameter(Procdbcomm, "@RoleID", DbType.Int32, RoleID);
                ds = db.ExecuteDataSet(Procdbcomm);
            }
            catch (Exception ex)
            {
                LogHelper.Error("根据条件筛选后台用户-GetPageList" + ex.ToString());
            }
            return ds;
        }


        /// <summary>
        /// 根据用户编号获取后台用户信息
        /// </summary>
        /// <param name="uid">用户编号</param>
        /// <returns>返回用户信息</returns>
        public DataTable GetUserInfo(long uid)
        {
            DataTable dtUser = new DataTable();

            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = "select * from UserInfo where IsDelete=0 and UID=@UID";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@UID", DbType.Int64, uid);
                var ds = db.ExecuteDataSet(Procdbcomm);
                if (ds != null && ds.Tables[0] != null)
                {
                    dtUser = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                //记录日志
                LogHelper.Error("根据用户编号获取后台用户信息-GetUserInfo" + ex.ToString());
            }
            return dtUser;
        }


        /// <summary>
        /// 后台用户添加
        /// </summary>
        /// <param name="loginName">用户名</param>
        /// <param name="password">密码</param>
        /// <param name="userStatus">用户状态 1正常 2暂停</param>
        /// <param name="mobile">手机号</param>
        /// <param name="email">邮箱</param>
        /// <param name="linkAddress">联系地址</param>
        /// <param name="remark">备注</param>
        /// <returns>返回添加是否成功,大于0成功,其他失败</returns>
        public int AddUserInfo(string loginName, string realName, string password, string createTime, int userStatus, string mobile, string email, string linkAddress, int isDelete, string remark, long supplier_id)
        {
            int ret = 0;

            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = @"insert into UserInfo (LoginName,RealName,Password,CreateTime,UserStatus,Mobile,Email,LinkAddress,IsDelete,Remark,supplier_id) 
                    values (@LoginName,@RealName,@Password,@CreateTime,@UserStatus,@Mobile,@Email,@LinkAddress,@IsDelete,@Remark,@supplier_id)";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@LoginName", DbType.String, loginName);
                db.AddInParameter(Procdbcomm, "@RealName", DbType.String, realName);
                db.AddInParameter(Procdbcomm, "@Password", DbType.String, password);
                db.AddInParameter(Procdbcomm, "@CreateTime", DbType.DateTime, createTime);
                db.AddInParameter(Procdbcomm, "@UserStatus", DbType.Int32, userStatus);
                db.AddInParameter(Procdbcomm, "@Mobile", DbType.String, mobile);
                db.AddInParameter(Procdbcomm, "@Email", DbType.String, email);
                db.AddInParameter(Procdbcomm, "@LinkAddress", DbType.String, linkAddress);
                db.AddInParameter(Procdbcomm, "@IsDelete", DbType.Int32, isDelete);
                db.AddInParameter(Procdbcomm, "@Remark", DbType.String, remark);
                db.AddInParameter(Procdbcomm, "@supplier_id", DbType.Int64, supplier_id);
                ret = db.ExecuteNonQuery(Procdbcomm);
            }
            catch (Exception ex)
            {
                //记录日志
                ret = -99;
                LogHelper.Error("后台用户添加-AddUserInfo" + ex.ToString());
            }
            return ret;
        }


        /// <summary>
        /// 修改后台用户
        /// </summary>
        /// <param name="uid">用户编号</param>
        /// <param name="realName">真实姓名</param>
        /// <param name="userStatus">用户状态</param>
        /// <param name="mobile">手机号</param>
        /// <param name="email">邮箱</param>
        /// <param name="linkAddress">联系地址</param>
        /// <param name="remark">备注</param>
        /// <returns>返回修改是否成功,大于0成功,其他失败</returns>
        public int EditUserInfo(long uid, string realName, int userStatus, string mobile, string email, string linkAddress, string remark)
        {
            int ret = 0;
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = @"update UserInfo set RealName =@RealName,UserStatus=@UserStatus,Mobile=@Mobile,Email=@Email,
                              LinkAddress =@LinkAddress,Remark =@Remark where UID=@UID";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@RealName", DbType.String, realName);
                db.AddInParameter(Procdbcomm, "@UserStatus", DbType.Int32, userStatus);
                db.AddInParameter(Procdbcomm, "@Mobile", DbType.String, mobile);
                db.AddInParameter(Procdbcomm, "@Email", DbType.String, email);
                db.AddInParameter(Procdbcomm, "@LinkAddress", DbType.String, linkAddress);
                db.AddInParameter(Procdbcomm, "@Remark", DbType.String, remark);
                db.AddInParameter(Procdbcomm, "@UID", DbType.Int64, uid);
                ret = db.ExecuteNonQuery(Procdbcomm);
            }
            catch (Exception ex)
            {
                //记录日志
                ret = -99;
                LogHelper.Error("修改后台用户-EditUserInfo" + ex.ToString());
            }
            return ret;
        }

        /// <summary>
        /// 删除后台用户（逻辑删除）
        /// </summary>
        /// <param name="uid">用户编号</param>
        /// <returns>删除是否成功</returns>
        public int DelUserInfo(long uid)
        {
            int ret = 0;
            try
            {

                DBHelper db = DBFactory.AdminDB;
                string strSql = @"update UserInfo set IsDelete =1 where UID=@UID";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@UID", DbType.Int64, uid);
                ret = db.ExecuteNonQuery(Procdbcomm);
            }
            catch (Exception ex)
            {
                //记录日志
                ret = -99;
                LogHelper.Error("删除后台用户-DelUserInfo" + ex.ToString());
            }
            return ret;
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="uid">用户编号</param>
        /// <param name="newpwd">新密码</param>
        /// <returns></returns>
        public int EditPwd(long uid, string newpwd)
        {
            int ret = 0;
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = @"update UserInfo set Password =@Password where UID=@UID";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@UID", DbType.Int64, uid);
                db.AddInParameter(Procdbcomm, "@Password", DbType.String, newpwd);
                ret = db.ExecuteNonQuery(Procdbcomm);
            }
            catch (Exception ex)
            {
                //记录日志
                ret = -99;
                LogHelper.Error("修改密码-EditPwd" + ex.ToString());
            }
            return ret;
        }


        /// <summary>
        /// 获取用户角色信息
        /// </summary>
        /// <param name="uid">用户编号</param>
        public DataTable GetUserRoleList(long uid)
        {
            DataTable dtUserRole = new DataTable("UserRole");
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = @"select ur.UID,ur.RoleID from UserRole as ur left join Role as r on ur.RoleID=r.RoleID 
                           where UID=@UID ";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@UID", DbType.Int64, uid);
                var ds = db.ExecuteDataSet(Procdbcomm);
                if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count != 0)
                {
                    dtUserRole = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                //记录日志
                LogHelper.Error("获取用户角色信息-GetUserRoleList" + ex.ToString());
            }
            return dtUserRole;
        }

        /// <summary>
        /// 删除用户角色关联
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public int DeleteUserRole(long uid)
        {
            int ret = 0;
            try
            {
                DBHelper db = DBFactory.AdminDB;
                string strSql = "delete from UserRole where UID=@UID";
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                db.AddInParameter(Procdbcomm, "@UID", DbType.Int64, uid);
                ret = db.ExecuteNonQuery(Procdbcomm);
            }
            catch (Exception ex)
            {
                //记录日志
                ret = -99;
                LogHelper.Error("删除用户角色关联-DeleteUserRole" + ex.ToString());
            }
            return ret;
        }

        /// <summary>
        /// 设置用户角色
        /// </summary>
        /// <param name="uid">用户编号</param>
        /// <param name="roleIds">角色id集合</param>
        /// <returns></returns>
        public int SetUserRole(long uid, string[] roleIds)
        {
            int ret = 0;

            try
            {
                StringBuilder str = new StringBuilder();
                DBHelper db = DBFactory.AdminDB;
                str.Append("insert into UserRole (UID,RoleID) values ");
                for (int i = 0; i < roleIds.Length; i++)
                {
                    str.Append("(@UID,@RoleID").Append(i.ToString()).Append("),");
                }
                //去掉字符串最后一个,逗号
                string strSql = str.ToString().Substring(0, str.Length - 1);
                DbCommand Procdbcomm = db.GetSqlStringCommand(strSql);
                for (int i = 0; i < roleIds.Length; i++)
                {
                    db.AddInParameter(Procdbcomm, "@RoleID" + i, DbType.Int64, roleIds[i]);
                }
                db.AddInParameter(Procdbcomm, "@UID", DbType.Int64, uid);
                ret = db.ExecuteNonQuery(Procdbcomm);
            }
            catch (Exception ex)
            {
                //记录日志
                ret = -99;
                LogHelper.Error("设置用户角色-SetUserRole" + ex.ToString());
            }
            return ret;
        }

        /// <summary>
        /// 根据条件筛选登录日志
        /// </summary>
        /// <param name="loginName"></param>
        /// <param name="begTime"></param>
        /// <param name="endTime"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public DataSet GetLoginLogPageList(string loginName, string begTime, string endTime, int pageIndex, int pageSize)
        {
            DataSet ds = new DataSet();
            try
            {
                DBHelper db = DBFactory.AdminDB;
                DbCommand Procdbcomm = db.GetStoredProcCommand("proc_loginlog_getlist");
                db.AddInParameter(Procdbcomm, "@loginName", DbType.String, loginName);
                db.AddInParameter(Procdbcomm, "@begTime", DbType.String, begTime);
                db.AddInParameter(Procdbcomm, "@endTime", DbType.String, endTime);
                db.AddInParameter(Procdbcomm, "@pageIndex", DbType.Int32, pageIndex);
                db.AddInParameter(Procdbcomm, "@pageRows", DbType.Int32, pageSize);
                ds = db.ExecuteDataSet(Procdbcomm);
            }
            catch (Exception ex)
            {
                LogHelper.Error("根据条件筛选登录日志-GetLoginLogPageList" + ex.ToString());
            }
            return ds;
        }


    }
}
